"""
04_basic_queries.py
===================
Basic Cypher Query Operations

This module covers:
1. CRUD operations (Create, Read, Update, Delete)
2. Pattern matching fundamentals
3. Filtering with WHERE clauses
4. Ordering and pagination
5. Aggregation basics

Author: Neo4j Tutorial
Date: January 2025
"""

from typing import Dict, List, Any, Optional
from dataclasses import dataclass
from datetime import datetime
import sys
import os

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from src.utils.connection import get_driver


class CypherCRUD:
    """
    Basic CRUD operations using Cypher queries.
    
    Cypher Pattern Syntax:
    
    ┌─────────────────────────────────────────────────────────────────┐
    │                    CYPHER PATTERNS                              │
    ├─────────────────────────────────────────────────────────────────┤
    │                                                                 │
    │  NODE:          (n:Label {prop: value})                        │
    │  RELATIONSHIP:  -[r:TYPE {prop: value}]->                      │
    │  PATH:          (a)-[r]->(b)                                   │
    │                                                                 │
    │  EXAMPLES:                                                      │
    │  ─────────                                                      │
    │  (p:Patient {id: "P001"})                                      │
    │  (p:Patient)-[:DIAGNOSED_WITH]->(d:Disease)                    │
    │  (p)-[r:TAKES {status: "active"}]->(m:Medication)             │
    │                                                                 │
    └─────────────────────────────────────────────────────────────────┘
    """
    
    def __init__(self, driver=None):
        self.driver = driver or get_driver()
    
    # =========================================================
    # CREATE OPERATIONS
    # =========================================================
    
    def create_node(
        self,
        label: str,
        properties: Dict[str, Any]
    ) -> Dict[str, Any]:
        """
        Create a single node with given label and properties.
        
        Cypher:
            CREATE (n:Label {prop1: $val1, prop2: $val2, ...})
            SET n.createdAt = datetime()
            RETURN n
        
        Args:
            label: Node label
            properties: Dictionary of properties
            
        Returns:
            Created node data
        """
        prop_keys = list(properties.keys())
        prop_str = ", ".join([f"{k}: ${k}" for k in prop_keys])
        
        query = f"""
        CREATE (n:{label} {{{prop_str}}})
        SET n.createdAt = datetime()
        RETURN n
        """
        
        with self.driver.session() as session:
            result = session.run(query, **properties)
            record = result.single()
            return dict(record["n"]) if record else None
    
    def create_with_merge(
        self,
        label: str,
        key_property: str,
        key_value: Any,
        additional_properties: Dict[str, Any] = None
    ) -> Dict[str, Any]:
        """
        Create or match node using MERGE (upsert pattern).
        
        MERGE creates the node only if it doesn't exist.
        ON CREATE: Runs when node is created
        ON MATCH: Runs when node already exists
        
        Args:
            label: Node label
            key_property: Property to match on
            key_value: Value to match
            additional_properties: Extra properties to set
            
        Returns:
            Node data (created or matched)
        """
        query = f"""
        MERGE (n:{label} {{{key_property}: $key_value}})
        ON CREATE SET n.createdAt = datetime()
        ON MATCH SET n.updatedAt = datetime()
        """
        
        if additional_properties:
            set_clauses = ", ".join([f"n.{k} = ${k}" for k in additional_properties])
            query += f"SET {set_clauses}\n"
        
        query += "RETURN n, n.createdAt = n.updatedAt as was_created"
        
        params = {"key_value": key_value}
        if additional_properties:
            params.update(additional_properties)
        
        with self.driver.session() as session:
            result = session.run(query, **params)
            record = result.single()
            if record:
                return {
                    "node": dict(record["n"]),
                    "was_created": record["was_created"]
                }
            return None
    
    def create_relationship(
        self,
        from_label: str,
        from_id: str,
        rel_type: str,
        to_label: str,
        to_id: str,
        properties: Dict[str, Any] = None
    ) -> Dict[str, Any]:
        """
        Create a relationship between two nodes.
        
        Args:
            from_label: Start node label
            from_id: Start node ID
            rel_type: Relationship type
            to_label: End node label
            to_id: End node ID
            properties: Relationship properties
            
        Returns:
            Relationship data
        """
        prop_str = ""
        if properties:
            prop_list = ", ".join([f"{k}: ${k}" for k in properties])
            prop_str = f" {{{prop_list}}}"
        
        query = f"""
        MATCH (a:{from_label} {{id: $from_id}})
        MATCH (b:{to_label} {{id: $to_id}})
        CREATE (a)-[r:{rel_type}{prop_str}]->(b)
        SET r.createdAt = datetime()
        RETURN a.id as from_node, type(r) as rel_type, b.id as to_node,
               properties(r) as rel_props
        """
        
        params = {"from_id": from_id, "to_id": to_id}
        if properties:
            params.update(properties)
        
        with self.driver.session() as session:
            result = session.run(query, **params)
            record = result.single()
            return record.data() if record else None
    
    # =========================================================
    # READ OPERATIONS
    # =========================================================
    
    def find_by_id(
        self,
        label: str,
        id_value: str
    ) -> Optional[Dict[str, Any]]:
        """
        Find a node by its ID property.
        
        Args:
            label: Node label
            id_value: ID value to find
            
        Returns:
            Node data or None
        """
        query = f"""
        MATCH (n:{label} {{id: $id}})
        RETURN n
        """
        
        with self.driver.session() as session:
            result = session.run(query, id=id_value)
            record = result.single()
            return dict(record["n"]) if record else None
    
    def find_all(
        self,
        label: str,
        limit: int = 100,
        skip: int = 0,
        order_by: str = None,
        order_desc: bool = False
    ) -> List[Dict[str, Any]]:
        """
        Find all nodes of a given label with pagination.
        
        Args:
            label: Node label
            limit: Maximum results
            skip: Results to skip
            order_by: Property to order by
            order_desc: Descending order
            
        Returns:
            List of nodes
        """
        order_clause = ""
        if order_by:
            direction = "DESC" if order_desc else "ASC"
            order_clause = f"ORDER BY n.{order_by} {direction}"
        
        query = f"""
        MATCH (n:{label})
        RETURN n
        {order_clause}
        SKIP $skip
        LIMIT $limit
        """
        
        with self.driver.session() as session:
            result = session.run(query, skip=skip, limit=limit)
            return [dict(r["n"]) for r in result]
    
    def find_with_filters(
        self,
        label: str,
        filters: Dict[str, Any],
        limit: int = 100
    ) -> List[Dict[str, Any]]:
        """
        Find nodes matching filter conditions.
        
        Supports equality filters. For complex queries,
        use find_with_where.
        
        Args:
            label: Node label
            filters: Property filters
            limit: Maximum results
            
        Returns:
            Matching nodes
        """
        where_clauses = [f"n.{k} = ${k}" for k in filters.keys()]
        where_str = " AND ".join(where_clauses)
        
        query = f"""
        MATCH (n:{label})
        WHERE {where_str}
        RETURN n
        LIMIT $limit
        """
        
        params = {"limit": limit, **filters}
        
        with self.driver.session() as session:
            result = session.run(query, **params)
            return [dict(r["n"]) for r in result]
    
    def find_with_where(
        self,
        label: str,
        where_clause: str,
        parameters: Dict[str, Any] = None,
        limit: int = 100
    ) -> List[Dict[str, Any]]:
        """
        Find nodes using custom WHERE clause.
        
        Examples:
            - "n.age > 40 AND n.active = true"
            - "n.name CONTAINS 'Smith'"
            - "n.createdAt > datetime('2024-01-01')"
        
        Args:
            label: Node label
            where_clause: WHERE condition (without WHERE keyword)
            parameters: Query parameters
            limit: Maximum results
            
        Returns:
            Matching nodes
        """
        query = f"""
        MATCH (n:{label})
        WHERE {where_clause}
        RETURN n
        LIMIT $limit
        """
        
        params = {"limit": limit}
        if parameters:
            params.update(parameters)
        
        with self.driver.session() as session:
            result = session.run(query, **params)
            return [dict(r["n"]) for r in result]
    
    def find_related(
        self,
        label: str,
        node_id: str,
        rel_type: str = None,
        direction: str = "OUTGOING"
    ) -> List[Dict[str, Any]]:
        """
        Find nodes related to a given node.
        
        Args:
            label: Source node label
            node_id: Source node ID
            rel_type: Relationship type filter
            direction: OUTGOING, INCOMING, or BOTH
            
        Returns:
            Related nodes with relationship info
        """
        rel_pattern = f":{rel_type}" if rel_type else ""
        
        if direction == "OUTGOING":
            pattern = f"-[r{rel_pattern}]->(related)"
        elif direction == "INCOMING":
            pattern = f"<-[r{rel_pattern}]-(related)"
        else:  # BOTH
            pattern = f"-[r{rel_pattern}]-(related)"
        
        query = f"""
        MATCH (n:{label} {{id: $id}}){pattern}
        RETURN labels(related)[0] as label,
               related.id as id,
               related.name as name,
               type(r) as relationship,
               properties(r) as rel_properties
        """
        
        with self.driver.session() as session:
            result = session.run(query, id=node_id)
            return [r.data() for r in result]
    
    # =========================================================
    # UPDATE OPERATIONS
    # =========================================================
    
    def update_properties(
        self,
        label: str,
        id_value: str,
        properties: Dict[str, Any]
    ) -> Optional[Dict[str, Any]]:
        """
        Update node properties using SET.
        
        Args:
            label: Node label
            id_value: Node ID
            properties: Properties to update
            
        Returns:
            Updated node or None
        """
        set_clauses = [f"n.{k} = ${k}" for k in properties.keys()]
        set_clauses.append("n.updatedAt = datetime()")
        set_str = ", ".join(set_clauses)
        
        query = f"""
        MATCH (n:{label} {{id: $id}})
        SET {set_str}
        RETURN n
        """
        
        params = {"id": id_value, **properties}
        
        with self.driver.session() as session:
            result = session.run(query, **params)
            record = result.single()
            return dict(record["n"]) if record else None
    
    def add_label(
        self,
        current_label: str,
        node_id: str,
        new_label: str
    ) -> bool:
        """Add a label to an existing node."""
        query = f"""
        MATCH (n:{current_label} {{id: $id}})
        SET n:{new_label}
        RETURN n
        """
        
        with self.driver.session() as session:
            result = session.run(query, id=node_id)
            return result.single() is not None
    
    def remove_label(
        self,
        current_label: str,
        node_id: str,
        label_to_remove: str
    ) -> bool:
        """Remove a label from a node."""
        query = f"""
        MATCH (n:{current_label} {{id: $id}})
        REMOVE n:{label_to_remove}
        RETURN n
        """
        
        with self.driver.session() as session:
            result = session.run(query, id=node_id)
            return result.single() is not None
    
    def remove_property(
        self,
        label: str,
        node_id: str,
        property_name: str
    ) -> bool:
        """Remove a property from a node."""
        query = f"""
        MATCH (n:{label} {{id: $id}})
        REMOVE n.{property_name}
        SET n.updatedAt = datetime()
        RETURN n
        """
        
        with self.driver.session() as session:
            result = session.run(query, id=node_id)
            return result.single() is not None
    
    # =========================================================
    # DELETE OPERATIONS
    # =========================================================
    
    def delete_node(
        self,
        label: str,
        id_value: str,
        detach: bool = True
    ) -> bool:
        """
        Delete a node.
        
        Args:
            label: Node label
            id_value: Node ID
            detach: If True, also delete relationships
            
        Returns:
            True if deleted
        """
        delete_cmd = "DETACH DELETE" if detach else "DELETE"
        
        query = f"""
        MATCH (n:{label} {{id: $id}})
        {delete_cmd} n
        RETURN count(*) as deleted
        """
        
        with self.driver.session() as session:
            result = session.run(query, id=id_value)
            return result.single()["deleted"] > 0
    
    def delete_relationship(
        self,
        from_label: str,
        from_id: str,
        rel_type: str,
        to_label: str,
        to_id: str
    ) -> bool:
        """Delete a specific relationship."""
        query = f"""
        MATCH (a:{from_label} {{id: $from_id}})-[r:{rel_type}]->(b:{to_label} {{id: $to_id}})
        DELETE r
        RETURN count(*) as deleted
        """
        
        with self.driver.session() as session:
            result = session.run(query, from_id=from_id, to_id=to_id)
            return result.single()["deleted"] > 0
    
    def delete_all_by_label(
        self,
        label: str
    ) -> int:
        """Delete all nodes with a specific label."""
        query = f"""
        MATCH (n:{label})
        DETACH DELETE n
        RETURN count(*) as deleted
        """
        
        with self.driver.session() as session:
            result = session.run(query)
            return result.single()["deleted"]
    
    # =========================================================
    # COUNTING AND AGGREGATION
    # =========================================================
    
    def count_nodes(
        self,
        label: str,
        filters: Dict[str, Any] = None
    ) -> int:
        """Count nodes matching criteria."""
        where_clause = ""
        params = {}
        
        if filters:
            where_clauses = [f"n.{k} = ${k}" for k in filters.keys()]
            where_clause = "WHERE " + " AND ".join(where_clauses)
            params = filters
        
        query = f"""
        MATCH (n:{label})
        {where_clause}
        RETURN count(n) as count
        """
        
        with self.driver.session() as session:
            result = session.run(query, **params)
            return result.single()["count"]
    
    def aggregate(
        self,
        label: str,
        group_by: str,
        aggregations: List[str]
    ) -> List[Dict[str, Any]]:
        """
        Perform aggregation query.
        
        Args:
            label: Node label
            group_by: Property to group by
            aggregations: List of aggregation expressions
                e.g., ["count(n) as total", "avg(n.age) as avg_age"]
        
        Returns:
            Aggregation results
        """
        agg_str = ", ".join(aggregations)
        
        query = f"""
        MATCH (n:{label})
        RETURN n.{group_by} as {group_by}, {agg_str}
        ORDER BY {aggregations[0].split(' as ')[-1]} DESC
        """
        
        with self.driver.session() as session:
            result = session.run(query)
            return [r.data() for r in result]


# ============================================================
# DEMONSTRATION
# ============================================================

if __name__ == "__main__":
    print("=" * 60)
    print("Basic Cypher Queries - Demonstration")
    print("=" * 60)
    
    try:
        crud = CypherCRUD()
        
        # CREATE examples
        print("\n📌 CREATE Operations")
        print("-" * 40)
        
        # Create node
        patient = crud.create_node("TestPatient", {
            "id": "TEST001",
            "name": "Test Patient",
            "age": 35
        })
        print(f"   Created: {patient}")
        
        # MERGE (upsert)
        result = crud.create_with_merge(
            "TestPatient", "id", "TEST001",
            {"name": "Updated Test Patient"}
        )
        print(f"   Merged: was_created={result['was_created']}")
        
        # READ examples
        print("\n📌 READ Operations")
        print("-" * 40)
        
        # Find by ID
        found = crud.find_by_id("TestPatient", "TEST001")
        print(f"   Found by ID: {found['name'] if found else 'Not found'}")
        
        # Find with WHERE
        patients = crud.find_with_where(
            "TestPatient",
            "n.age > $min_age",
            {"min_age": 30}
        )
        print(f"   Found with filter: {len(patients)} patients")
        
        # UPDATE examples
        print("\n📌 UPDATE Operations")
        print("-" * 40)
        
        updated = crud.update_properties(
            "TestPatient", "TEST001",
            {"age": 36, "email": "test@example.com"}
        )
        print(f"   Updated: age={updated['age']}")
        
        # DELETE examples
        print("\n📌 DELETE Operations")
        print("-" * 40)
        
        deleted = crud.delete_node("TestPatient", "TEST001")
        print(f"   Deleted: {deleted}")
        
        print("\n" + "=" * 60)
        print("✅ Basic queries demonstration complete!")
        print("=" * 60)
        
    except Exception as e:
        print(f"Error: {e}")
